
CREATE   trigger tft_msquantity_bi on dbo.tft_msquantity
instead of insert
as
begin
  set nocount on
  declare  @nbalyear integer   
  declare  @nbalmonth integer   
   
  declare  @bmbala decimal(38,8)   
  declare  @bysumdebit decimal(38,8)   
  declare  @bysumcredit decimal(38,8)   
  declare  @tmsumdebit decimal(38,8)   
  declare  @tmsumcredit decimal(38,8)   
  declare  @tmsum decimal(38,8)   
   
  declare  @fbmbala decimal(38,8)   
  declare  @fbysumdebit decimal(38,8)   
  declare  @fbysumcredit decimal(38,8)   
  declare  @ftmsumdebit decimal(38,8)   
  declare  @ftmsumcredit decimal(38,8)   
  declare  @ftmsum decimal(38,8)   
   
  declare  @bmqtybala decimal(38,8)   
  declare  @byqtysumcredit decimal(38,8)   
  declare  @byqtysumdebit decimal(38,8)   
  declare  @tmqtysumcredit decimal(38,8)   
  declare  @tmqtysumdebit decimal(38,8)   
   
  declare  @nsubjectid integer
  declare  @nyear integer   
  declare  @nmonth integer   
  declare  @nfilid integer   
  declare  @nmoneyid integer   
  declare  @nbmbala decimal(38,8)   
  declare  @nbysumdebit decimal(38,8)   
  declare  @nbysumcredit decimal(38,8)   
  declare  @ntmsumdebit decimal(38,8)   
  declare  @ntmsumcredit decimal(38,8)   
  declare  @ntmsum decimal(38,8)   
  declare  @nfbmbala decimal(38,8)   
  declare  @nfbysumdebit decimal(38,8)   
  declare  @nfbysumcredit decimal(38,8)   
  declare  @nftmsumdebit decimal(38,8)   
  declare  @nftmsumcredit decimal(38,8)   
  declare  @nftmsum decimal(38,8)   
  declare  @nbmqtybala decimal(38,8)   
  declare  @nbyqtysumcredit decimal(38,8)   
  declare  @nbyqtysumdebit decimal(38,8)   
  declare  @ntmqtysumcredit decimal(38,8)   
  declare  @ntmqtysumdebit decimal(38,8)   
  declare  @nodata integer   

  declare @local_tab table(
	[subjectid] int  not null ,
	[nyear] int not null ,
	[nmonth] int not null ,
        [filid] int not null,
        [moneyid] int not null,
	[bmbala]    decimal(38,8),
	[bysumdebit]   decimal(38,8),
	[bysumcredit]  decimal(38,8),
	[tmsumdebit]  decimal(38,8),
	[tmsumcredit]   decimal(38,8),
	[tmsum]  decimal(38,8),
	[fbmbala]   decimal(38,8),
	[fbysumdebit]   decimal(38,8),
	[fbysumcredit]   decimal(38,8),
	[ftmsumdebit]   decimal(38,8),
	[ftmsumcredit]  decimal(38,8),
	[ftmsum]  decimal(38,8),
	[bmqtybala]  decimal(38,8),
	[byqtysumdebit]  decimal(38,8) ,
	[byqtysumcredit]  decimal(38,8),
	[tmqtysumdebit]  decimal(38,8),
	[tmqtysumcredit]  decimal(38,8),
        [odata] int )

  insert into @local_tab( 
     subjectid, nyear, nmonth,filid, moneyid,bmbala , bysumdebit, bysumcredit,
     tmsumdebit,tmsumcredit,tmsum, fbmbala, fbysumdebit, fbysumcredit,
     ftmsumdebit , ftmsumcredit, ftmsum, bmqtybala, byqtysumcredit,
     byqtysumdebit, tmqtysumcredit, tmqtysumdebit,odata   ) 
    select subjectid, nyear, nmonth,filid,moneyid, bmbala , bysumdebit, bysumcredit,
      tmsumdebit,tmsumcredit,tmsum, fbmbala, fbysumdebit, fbysumcredit,
      ftmsumdebit , ftmsumcredit, ftmsum, bmqtybala, byqtysumcredit,
      byqtysumdebit, tmqtysumcredit, tmqtysumdebit ,odata
      from inserted
  declare cur_msquantity cursor for
  select subjectid, nyear, nmonth,filid,moneyid, bmbala , bysumdebit, bysumcredit,
         tmsumdebit,tmsumcredit,tmsum, fbmbala, fbysumdebit, fbysumcredit,
         ftmsumdebit , ftmsumcredit, ftmsum, bmqtybala, byqtysumcredit,
         byqtysumdebit, tmqtysumcredit, tmqtysumdebit ,odata
    from @local_tab
 
  open cur_msquantity 
  fetch next from cur_msquantity
  into @nsubjectid, @nyear, @nmonth,@nfilid,@nmoneyid, @nbmbala , @nbysumdebit, @nbysumcredit,
       @ntmsumdebit,@ntmsumcredit,@ntmsum, @nfbmbala, @nfbysumdebit, @nfbysumcredit,
       @nftmsumdebit , @nftmsumcredit, @nftmsum, @nbmqtybala, @nbyqtysumcredit,
       @nbyqtysumdebit, @ntmqtysumcredit, @ntmqtysumdebit, @nodata
  while @@fetch_status = 0 
  begin    
    set  @nbalyear=NULL
    set  @nbalmonth=NULL
    set  @bmbala = 0
    set  @bysumdebit = 0
    set  @bysumcredit = 0
    set  @tmsumdebit = 0
    set  @tmsumcredit = 0
    set  @tmsum = 0
    set  @fbmbala = 0
    set  @fbysumdebit = 0
    set  @fbysumcredit = 0
    set  @ftmsumdebit = 0
    set  @ftmsumcredit = 0
    set  @ftmsum = 0
    set  @bmqtybala =  0
    set  @byqtysumcredit = 0
    set  @byqtysumdebit =  0
    set  @tmqtysumcredit = 0
    set  @tmqtysumdebit =  0
 
    select @nbalyear = nbalyear,
           @nbalmonth = nbalmonth,
           @bmbala = bmbala,
           @bysumdebit = bysumdebit,
           @bysumcredit = bysumcredit,
           @tmsumdebit = tmsumdebit,
           @tmsumcredit = tmsumcredit,
           @tmsum = tmsum,
           @fbmbala = fbmbala,
           @fbysumdebit = fbysumdebit,
           @fbysumcredit = fbysumcredit,
           @ftmsumdebit = ftmsumdebit,
           @ftmsumcredit = ftmsumcredit,
           @ftmsum = ftmsum,
           @bmqtybala = bmqtybala,
           @byqtysumcredit = byqtysumcredit,
           @byqtysumdebit = byqtysumdebit,
           @tmqtysumcredit = tmqtysumcredit,
           @tmqtysumdebit = tmqtysumdebit      
       from f_msquantitygetpriorinfo(@nmoneyid,@nsubjectid,@nyear,@nmonth,@nfilid)
      
    if ( @bmbala is null)       set @bmbala =0   
    if ( @bysumcredit is null)  set @bysumcredit =0   
    if ( @bysumdebit is null)   set @bysumdebit =0   
    if ( @tmsumcredit is null)  set @tmsumcredit =0   
    if ( @tmsumdebit is null)   set @tmsumdebit =0   
   
    set @nbysumcredit=@tmsumcredit + @bysumcredit   
    set @nbysumdebit =@tmsumdebit  + @bysumdebit    
   
    set @nbmbala=@bmbala+@tmsumdebit-@tmsumcredit   
   
    if ( @fbmbala is null)       set @fbmbala =0   
    if ( @fbysumcredit is null)  set @fbysumcredit =0   
    if ( @fbysumdebit is null)   set @fbysumdebit =0   
    if ( @ftmsumcredit is null)  set @ftmsumcredit =0   
    if ( @ftmsumdebit is null)   set @ftmsumdebit =0   
   
    set @nfbysumcredit=@ftmsumcredit + @fbysumcredit   
    set @nfbysumdebit =@ftmsumdebit  + @fbysumdebit    
   
    set @nfbmbala=@fbmbala+@ftmsumdebit-@ftmsumcredit   
  
    if ( @bmqtybala is null)       set @bmqtybala =0   
    if ( @byqtysumcredit is null)  set @byqtysumcredit =0   
    if ( @byqtysumdebit is null)   set @byqtysumdebit =0   
    if ( @tmqtysumcredit is null)  set @tmqtysumcredit =0   
    if ( @tmqtysumdebit is null)   set @tmqtysumdebit =0   
  
    set @nbyqtysumcredit=@tmqtysumcredit + @byqtysumcredit   
    set @nbyqtysumdebit =@tmqtysumdebit  + @byqtysumdebit    
   
    set @nbmqtybala=@bmqtybala+@tmqtysumdebit-@tmqtysumcredit   
       
    if (@nyear<>@nbalyear)    
    begin   
      set @nbysumcredit=0   
      set @nbysumdebit=0   
      set @nfbysumcredit=0   
      set @nfbysumdebit=0   
      set @nbyqtysumcredit=0   
      set @nbyqtysumdebit=0   
    end   
    insert into tft_msquantity(
      subjectid, nyear, nmonth,filid, moneyid,bmbala , bysumdebit, bysumcredit,
      tmsumdebit,tmsumcredit,tmsum, fbmbala, fbysumdebit, fbysumcredit,
      ftmsumdebit , ftmsumcredit, ftmsum, bmqtybala, byqtysumcredit,
      byqtysumdebit, tmqtysumcredit, tmqtysumdebit ,odata    )
    values( @nsubjectid, @nyear, @nmonth,@nfilid,@nmoneyid,isnull(@nbmbala,0) ,isnull( @nbysumdebit,0),
      isnull(@nbysumcredit,0), isnull(@ntmsumdebit,0),isnull(@ntmsumcredit,0),isnull(@ntmsum,0), isnull(@nfbmbala,0),
      isnull(@nfbysumdebit,0),isnull(@nfbysumcredit,0),isnull(@nftmsumdebit,0) ,isnull( @nftmsumcredit,0), 
      isnull(@nftmsum,0), isnull(@nbmqtybala,0), isnull(@nbyqtysumcredit,0),
      isnull(@nbyqtysumdebit,0),isnull(@ntmqtysumcredit,0), isnull(@ntmqtysumdebit,0),@nodata )
   fetch next from  cur_msquantity
   into @nsubjectid, @nyear, @nmonth,@nfilid,@nmoneyid, @nbmbala , @nbysumdebit, @nbysumcredit,
        @ntmsumdebit,@ntmsumcredit,@ntmsum, @nfbmbala, @nfbysumdebit, @nfbysumcredit,
        @nftmsumdebit , @nftmsumcredit, @nftmsum, @nbmqtybala, @nbyqtysumcredit,
        @nbyqtysumdebit, @ntmqtysumcredit, @ntmqtysumdebit,@nodata 
   end
   close cur_msquantity
   deallocate  cur_msquantity
 
end

